from sqlalchemy.orm import Session
from sqlalchemy import text
from typing import List, Optional
from app.models.user import UserEntity, UserCreate, UserUpdate

class UserRepository:
    def __init__(self, db: Session):
        self.db = db

    def get_max_row_index(self) -> int:
        """获取用户表的最大行号"""
        sql = text("""
            SELECT COALESCE(MAX(row_index), 0)
            FROM users
        """)
        result = self.db.execute(sql)
        max_row_index = result.scalar()
        return max_row_index or 0

    def create(self, user_create: UserCreate) -> UserEntity:
        """创建新用户"""
        # 获取最大行号并加1
        new_row_index = self.get_max_row_index() + 1
        
        # 创建用户实例并设置行号
        user_data = user_create.dict()
        db_user = UserEntity(**user_data, row_index=new_row_index)
        
        self.db.add(db_user)
        self.db.commit()
        self.db.refresh(db_user)
        return db_user

    def get_by_id(self, user_id: int) -> Optional[UserEntity]:
        """通过ID获取用户"""
        return self.db.query(UserEntity).filter(UserEntity.id == user_id).first()

    def get_by_email(self, email: str) -> Optional[UserEntity]:
        """通过邮箱获取用户"""
        return self.db.query(UserEntity).filter(UserEntity.email == email).first()

    def get_list(self, skip: int = 0, limit: int = 100) -> List[UserEntity]:
        """获取用户列表"""
        return self.db.query(UserEntity).offset(skip).limit(limit).all()

    def update(self, user_id: int, user_update: UserUpdate) -> Optional[UserEntity]:
        """更新用户信息"""
        db_user = self.get_by_id(user_id)
        if not db_user:
            return None
            
        update_data = user_update.dict(exclude_unset=True)
        for field, value in update_data.items():
            setattr(db_user, field, value)
            
        self.db.commit()
        self.db.refresh(db_user)
        return db_user

    def delete(self, user_id: int) -> bool:
        """删除用户"""
        db_user = self.get_by_id(user_id)
        if not db_user:
            return False
            
        self.db.delete(db_user)
        self.db.commit()
        return True

    # 可以在这里添加其他自定义SQL查询方法
